﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using LKVMLMLib;
using System.Web;
using System.ServiceModel.Channels;

namespace LKVService
{
    public class ProfitService
    {
        public string GetData(int value)
        {
            return string.Format("-- : {0}", value);
        }
       
        public Profit[] getListProfit(DateTime? from=null, DateTime? to=null)
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                {
                    db.Configuration.ProxyCreationEnabled = false;
                    if (from == null && to == null)
                        return db.Profits.ToArray();
                    return db.Profits.Where(w => w.CreateDate >= from && w.CreateDate <= to).ToArray();
                }
            }
        }    
        // xu ly table Commission 
        public CommissionConfig[] getListCommissionConfig(bool status = false,bool monthly=false )
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var q = db.CommissionConfigs.AsQueryable();

                if (status == true)
                    q = q.Where(w => w.Status == status);
                if (monthly == true)
                    q = q.Where(w => w.Monthly == monthly);
                q = q.OrderBy(s => s.STT);
                return q.ToArray();
            }
        }
        public int UpdateCommission(CommissionConfig c)
        {
            int _rt = 0;
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var Cm = db.CommissionConfigs.Where(w => w.CommissionName == c.CommissionName).SingleOrDefault();
                Cm.Status = c.Status;
                db.SaveChanges();
                _rt = 1;
            }
            return _rt;
        }
      // xu ly commission month year
       public  CommissionMonthYear[] getListCommissionMonthYear()
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var rt = db.CommissionMonthYears;
                return rt.ToArray();
            }
        }
       public CommissionMonthYear[] FindCommissionMonthYear(int month, int year)
       {
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
           {
               db.Configuration.ProxyCreationEnabled = false;
               return db.CommissionMonthYears.Where(x => x.Month == month && x.Year == year).ToArray();
           }
       }
        public int AddCommissionMonthYear (CommissionMonthYear c)
       {
           int _rt = 0;
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                CommissionMonthYear DTNEW = new CommissionMonthYear();
                DTNEW.CommissionName = c.CommissionName;
                DTNEW.Description = c.Description;
                DTNEW.Month = c.Month;
                DTNEW.Year = c.Year;
                DTNEW.Status = c.Status;
                db.CommissionMonthYears.Add(DTNEW);
                db.SaveChanges();
                _rt = 1; 
            }
           return _rt;
       }
       // xu ly commission date
       public CommissionDate[] getListCommissionDate()
       {
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
           {
               db.Configuration.ProxyCreationEnabled = false;
               var rt = db.CommissionDates;
               return rt.ToArray();
           }
       }
       public CommissionDate[] findCommissionDatebyDate(DateTime date)
       {
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
           {
               db.Configuration.ProxyCreationEnabled = false;
               return db.CommissionDates.Where(x => x.Date == date).ToArray(); 
           }
       }
       public CommissionDate chooseCommissionDate(string name ,DateTime date)
       {
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
           {
               db.Configuration.ProxyCreationEnabled = false;
               return db.CommissionDates.Where(x => x.CommissionName ==name && x.Date ==date).FirstOrDefault();
           }
       }
       public CommissionDate findComissionDatebyID(int id )
       {
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
           {
               db.Configuration.ProxyCreationEnabled = false;
               return db.CommissionDates.Where(x => x.ID == id).FirstOrDefault();
           }
       }
       public int AddCommissionDate(CommissionDate c)
       {
           int _rt = 0;
           string connection = DuAnDaCapEntities.getDomain(); 
           using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
           {
               CommissionDate DTNEW = new CommissionDate();
               DTNEW.CommissionName = c.CommissionName;
               DTNEW.Descriptiom = c.Descriptiom;            
               DTNEW.Date = c.Date;
               DTNEW.Status = c.Status;
               db.CommissionDates.Add(DTNEW);
               db.SaveChanges();
               _rt = 1;
           }
           return _rt;
       }
        public CommissionForm[] getlistCommission(string TxtSerch, string cboUser, string cboProfitType, string Cbomonth, string Cboyear, int ck)
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var sql = from U in db.UserProfiles
                          join P in db.Profits.Where(w=>w.Amount>0)
                           on U.UserId equals P.UserId
                          join C in db.CommissionConfigs
                          on P.ProfitType equals C.CommissionName
                          select new CommissionForm
                          {
                              STT = P.ID,
                              UserName = U.UserName,
                              HoTen = U.HOTEN,
                              CreateDate = P.CreateDate,
                              Amount = P.Amount,
                              ProfitType = C.CommissionDesc,
                              Decription = P.Decription,
                              PV = P.PV,
                              UserId = U.UserId,
                              CommissionName = P.ProfitType,
                              CMND = U.SOCMND
                          };
                sql = sql.Where(w => w.CreateDate.Value.Month.ToString() == Cbomonth && w.CreateDate.Value.Year.ToString() == Cboyear);
                var ckcf = db.ChuKyConfigs.Where(w => w.ChuKyID == ck).SingleOrDefault();
                if (ckcf != null)
                    sql = sql.Where(w => w.CreateDate.Value.Day >= ckcf.From && w.CreateDate.Value.Day <= ckcf.To);
                if (TxtSerch != null)
                {
                    // search 
                    sql = sql.Where(w => w.UserName.Contains(TxtSerch) || w.ProfitType.Contains(TxtSerch));
                }
                if (cboProfitType != null && cboProfitType != string.Empty)
                    sql = sql.Where(w => w.CommissionName == cboProfitType);
                if (cboUser != null && cboUser != string.Empty)
                    return sql.Where(delegate(CommissionForm w)
                    {
                        return (w.UserName == cboUser);
                    }).ToArray();

                return sql.OrderByDescending(w => w.STT).ToArray();
            }
        }
        public CommissionForm[] getlistCommissionByCMND(string TxtSerch, string CMND, string cboProfitType, string Cbomonth, string Cboyear, int ck)
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var sql = from U in db.UserProfiles
                          join P in db.Profits
                           on U.UserId equals P.UserId
                          join C in db.CommissionConfigs
                          on P.ProfitType equals C.CommissionName

                          select new CommissionForm
                          {
                              STT = P.ID,
                              UserName = U.UserName,
                              HoTen = U.HOTEN,
                              CreateDate = P.CreateDate,
                              Amount = P.Amount,
                              ProfitType = C.CommissionDesc,
                              Decription = P.Decription,
                              PV = P.PV,
                              UserId = U.UserId,
                              CommissionName = P.ProfitType,
                              CMND = U.SOCMND
                          };
                sql = sql.Where(w => w.CreateDate.Value.Month.ToString() == Cbomonth && w.CreateDate.Value.Year.ToString() == Cboyear);
                var ckcf = db.ChuKyConfigs.Where(w => w.ChuKyID == ck).SingleOrDefault();
                if (ckcf != null)
                    sql = sql.Where(w => w.CreateDate.Value.Day >= ckcf.From && w.CreateDate.Value.Day <= ckcf.To);
                if (TxtSerch != null)
                {
                    // search 
                    sql = sql.Where(w => w.UserName.Contains(TxtSerch) || w.ProfitType.Contains(TxtSerch));
                }
                if (cboProfitType != null && cboProfitType != string.Empty)
                    sql = sql.Where(w => w.CommissionName == cboProfitType);
                if (CMND != null && CMND != string.Empty)
                    return sql.Where(delegate(CommissionForm w)
                    {

                        return (w.CMND == CMND);
                    }).ToArray();

                return sql.OrderByDescending(w => w.STT).ToArray();
            }
        }
        public Tuple<int, double, CommissionForm[]> getlistCommissionPaging(string TxtSerch, string cboUser, string cboProfitType, int Cbomonth, int Cboyear, int ck, int fromRange = 0, int toRange = 100)
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var sql = from U in db.UserProfiles
                          join P in db.Profits
                           on U.UserId equals P.UserId
                          join C in db.CommissionConfigs
                          on P.ProfitType equals C.CommissionName
                          select new CommissionForm
                          {
                              STT = P.ID,
                              UserName = U.UserName,
                              HoTen = U.HOTEN,
                              CreateDate = P.CreateDate,
                              Amount = P.Amount,
                              ProfitType = C.CommissionDesc,
                              Decription = P.Decription,
                              PV = P.PV,
                              UserId = U.UserId,
                              CommissionName = P.ProfitType,
                              CMND = U.SOCMND
                          };
                sql = sql.Where(w => w.CreateDate.Value.Month == Cbomonth && w.CreateDate.Value.Year == Cboyear);
                var ckcf = db.ChuKyConfigs.Where(w => w.ChuKyID == ck).SingleOrDefault();
                if (ckcf != null)
                    sql = sql.Where(w => w.CreateDate.Value.Day >= ckcf.From && w.CreateDate.Value.Day <= ckcf.To);
                if (TxtSerch != null)
                {
                    // search 
                    sql = sql.Where(w => w.UserName.Contains(TxtSerch) || w.ProfitType.Contains(TxtSerch));
                }
                if (cboProfitType != null && cboProfitType != string.Empty)
                    sql = sql.Where(w => w.CommissionName == cboProfitType);
                if (cboUser != null && cboUser != string.Empty)
                    sql = sql.Where(delegate(CommissionForm w)
                    {
                        return (w.UserName == cboUser);
                    }).AsQueryable();

                int _totalItem = sql.Count();
                double _sumAmount = Convert.ToDouble(sql.Sum(x => x.Amount));
                if(fromRange == 1 || fromRange == 0)
                    return new Tuple<int, double, CommissionForm[]>(_totalItem, _sumAmount, sql.OrderByDescending(w => w.STT).Skip(0).Take(toRange).ToArray());
                else 
                    return new Tuple<int, double, CommissionForm[]>(_totalItem, _sumAmount, sql.OrderByDescending(w => w.STT).Skip(fromRange).Take(toRange).ToArray());
            }
        }
        public class CommissionForm
        {
            public int? STT;
            public string UserName;
            public string HoTen;
            public DateTime? CreateDate;
            public double? Amount;
            public string ProfitType;
            public string Decription;
            public double? PV;
            public int UserId;
            public string CommissionName;
            public string CMND;
        }
        // xu ly table Exchange
        public ExchangeConfig[] getListExchange()
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var rt = db.ExchangeConfigs.ToArray();
                return rt;
            }
        }
        public int UpdateExchange(ExchangeConfig c)
        {
            int _Return = 0;
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var d = db.ExchangeConfigs.Where(w => w.ID == c.ID).FirstOrDefault();
                d.Name = c.Name;
                d.Value = c.Value;
                db.SaveChanges();
                _Return = 1;
            }
            return _Return;
        }
       // xu ly phan quan ly doanh thu
        public double SumOder(DateTime Dau, DateTime Cuoi)
        {
            double _Sum = 0;
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var Sum_Order = from U in db.Orders
                                where U.Date >= Dau && U.Date <= Cuoi && U.Action == true

                                select new
                                {
                                    Sum = U.Amount * U.numpackage
                                };

                _Sum= Convert.ToDouble(Sum_Order.AsEnumerable().Sum(o => o.Sum));
              
              

            }
            return _Sum;
        }
        public double SumProfit (DateTime Dau  , DateTime Cuoi)
        {
            double _Sum = 0;
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {

                var Sum_OrderforCommission = from U in db.Profits

                                             where U.CreateDate >=Dau && U.CreateDate <= Cuoi
                                             select new
                                             {
                                                 Sum = U.Amount
                                             };
              _Sum= Convert.ToDouble(Sum_OrderforCommission.AsEnumerable().Sum(o => o.Sum));



            }
            return _Sum;
        }
        public ProfitUser[] profitMonth(DateTime? _dateDefault, int userid, string type)
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                int id = Convert.ToInt32(userid);
                var user = db.UserProfiles.Where(w => w.UserId == id).FirstOrDefault();           
                int it = 0;                      
                var model = db.Profits.Where(delegate(Profit o)
                {
                    bool result = true;
                    if (type != string.Empty && type != null)
                        result = result && o.ProfitType == type;
                    if(_dateDefault != null)
                        result = result && o.CreateDate.Value.Year == _dateDefault.Value.Year && o.CreateDate.Value.Month == _dateDefault.Value.Month && o.UserId == userid && o.CreateDate.Value.Day >= 16 && o.CreateDate.Value.Day <= 31;
                    if (user != null)
                        result = result && o.UserId == user.UserId;
                    return result;
                }).Select(
                obj => new ProfitUser
                {
                    Date = obj.CreateDate,
                    Description = obj.Decription,
                    HoTen = user.HOTEN,
                    UserId = user.UserId,
                    UserName = user.UserName,
                    Amount = obj.Amount,
                    Stt = it
                });
                    return model.OrderByDescending(o=>o.Date.Value).ToArray();
            }

            
        }
        public void ExeCuteMonthCommission(int month,int year)
        {
            object[] parameters = { month, year };
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var plugins = db.CommissionConfigs.Where(t => (t.Status == true) && t.Monthly == true);
                foreach (var plugin in plugins.ToList())
                {
                    string plugin_name = plugin.CommissionName + ".ExecuteCommission";
                    try
                    {
                        var instance = System.Activator.CreateInstance(Type.GetType(plugin_name + ", " + plugin.CommissionName.Substring(7) + "Service"));
                        var class_name = Type.GetType(plugin_name + ", " + plugin.CommissionName.Substring(7) + "Service");
                        var method = class_name.GetMethod("executeMonthly");
                        method.Invoke(instance, parameters);
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
            }
        }
        public void ExeCuteDaiyCommission(DateTime today)
        {
            DateTime toDay = DateTime.Now.Date;
            object[] parameters = { toDay };
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var plugins = db.CommissionConfigs.Where(t => (t.Status == true));
                foreach (var plugin in plugins.ToList())
                {
                    string plugin_name = plugin.CommissionName + ".ExecuteCommission";
                    try
                    {
                        
                        var instance = System.Activator.CreateInstance(Type.GetType(plugin_name + ", " + plugin.CommissionName.Substring(7) + "Service"));
                        var class_name = Type.GetType(plugin_name + ", " + plugin.CommissionName.Substring(7) + "Service");
                        var method = class_name.GetMethod("executeDaily");
                        method.Invoke(instance, parameters);
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
            }
        }
        public void ExeCuteRemake(DateTime today)
        {
            DateTime toDay = DateTime.Now.Date;
            object[] parameters = { toDay };
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var hh = new HoaHongTrucTiep.ExecuteCommission();
                var plugins = db.CommissionConfigs.Where(t => (t.Status == true));
                foreach (var plugin in plugins.ToList())
                {
                    string plugin_name = plugin.CommissionName + ".ExecuteCommission";
                    try
                    {

                        var instance = System.Activator.CreateInstance(Type.GetType(plugin_name + ", " + plugin.CommissionName.Substring(7) + "Service"));
                        var class_name = Type.GetType(plugin_name + ", " + plugin.CommissionName.Substring(7) + "Service");
                        var method = class_name.GetMethod("executeRemake");
                        method.Invoke(instance, parameters);
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
            }
        }

        public ChuKyConfig[] getChukyConfig()
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                {
                    db.Configuration.ProxyCreationEnabled = false;
                    var rt = db.ChuKyConfigs.ToArray();
                    return rt;
                }
            }
        }
        public ChuKyConfig findChuKyByID(int id)
        {
            string connection = DuAnDaCapEntities.getDomain(); 
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                return   db.ChuKyConfigs.Where(w => w.ChuKyID == id).FirstOrDefault();        
            }
        }
        public Profit[] findProfitbyUserID(int userid)
        {
             string connection = DuAnDaCapEntities.getDomain();
             using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
             { 
                 return db.Profits.Where(w=>w.UserId==userid).ToArray();
             }
        }
        public Order[] findOrderbyUserid(int userid)
        {
             string connection = DuAnDaCapEntities.getDomain();
             using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
             {
                 return db.Orders.Where(w => w.UserId == userid).ToArray();
             }
        }
        public CommissionForm[] SumCommissionByCMND(string TxtSerch, string CMND)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var sql = from U in db.UserProfiles
                          join P in db.Profits
                           on U.UserId equals P.UserId
                          join C in db.CommissionConfigs
                          on P.ProfitType equals C.CommissionName

                          select new CommissionForm
                          {
                              STT = P.ID,
                              UserName = U.UserName,
                              HoTen = U.HOTEN,
                              CreateDate = P.CreateDate,
                              Amount = P.Amount,
                              ProfitType = C.CommissionDesc,
                              Decription = P.Decription,
                              PV = P.PV,
                              UserId = U.UserId,
                              CommissionName = P.ProfitType,
                              CMND = U.SOCMND
                          };                       
                if (TxtSerch != null)
                {
                    // search 
                    sql = sql.Where(w => w.UserName.Contains(TxtSerch) || w.ProfitType.Contains(TxtSerch));
                }
                if (CMND != null && CMND != string.Empty)
                    return sql.Where(delegate(CommissionForm w)
                    {

                        return (w.CMND == CMND);
                    }).ToArray();

                return sql.OrderByDescending(w => w.STT).ToArray();
            }
        }
        public Profit[] findProfitbyUserIDInDate(int userid)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                DateTime a = DateTime.Now.Date;
                return db.Profits.Where(w => w.UserId == userid && w.CreateDate.Value==a).ToArray();
            }
        }
        // hoang viet     
        public Tuple<double, double, double, double, double> AddFundStatisics()
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                double NewFund = 0;
                double Today = 0;
                double Yesterday = 0;
                double ThisWeek = 0;
                double LastWeek = 0;
                DateTime yesterday = DateTime.Now.AddDays(-1);
                var _get1RowTowalletHistory = db.WalletHistories.Where(w => w.WalletId == 1 && w.Amount > 0).OrderByDescending(s => s.ID).Take(1).FirstOrDefault();
                if (_get1RowTowalletHistory != null)
                    NewFund = Convert.ToDouble(_get1RowTowalletHistory.Amount);
                // lay danh sach nhung thang co ngay tao khac null
                var _ListWalletHistory = db.WalletHistories.Where(w => w.CreateDate != null).ToList();
                if (_ListWalletHistory.Count > 0)
                {
                    var _getTodayForWalletHistory = _ListWalletHistory.Where(w => w.CreateDate.Date == DateTime.Now.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_getTodayForWalletHistory.Count > 0)
                        Today = Convert.ToDouble(_getTodayForWalletHistory.Sum(s => s.Amount));

                    var _getYesterdayToWallethistory = _ListWalletHistory.Where(w => w.CreateDate.Date == yesterday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_getYesterdayToWallethistory.Count > 0)
                        Yesterday = Convert.ToDouble(_getYesterdayToWallethistory.Sum(s => s.Amount));

                    var _ListFromToDateofWeek = _GetThisWeekAndLastWeek(DateTime.Now);
                    if (_ListFromToDateofWeek != null)
                    {
                        var _getThisWeekToWallethistory = _ListWalletHistory.Where(w =>w.WalletId == 1 && w.Amount > 0 && w.CreateDate.Date >= _ListFromToDateofWeek.Item1.Date && w.CreateDate.Date <= _ListFromToDateofWeek.Item2.Date).ToList();
                        if (_getThisWeekToWallethistory.Count > 0)
                            ThisWeek = Convert.ToDouble(_getThisWeekToWallethistory.Sum(s => s.Amount));

                        var _getLastWeekToWalletHistory = _ListWalletHistory.Where(w =>w.WalletId == 1 && w.Amount > 0 && w.CreateDate.Date >= _ListFromToDateofWeek.Item3.Date && w.CreateDate.Date <= _ListFromToDateofWeek.Item4.Date).ToList();
                        if (_getLastWeekToWalletHistory.Count > 0)
                            LastWeek = Convert.ToDouble(_getLastWeekToWalletHistory.Sum(s => s.Amount));
                    }
                }
                Tuple<double, double, double, double, double> regain = new Tuple<double, double, double, double, double>(NewFund, Today, Yesterday, ThisWeek, LastWeek);
                return regain;
            }
        }
        public Tuple<DateTime, DateTime, DateTime, DateTime> _GetThisWeekAndLastWeek(DateTime c)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                DateTime FromDateToThisWeek = DateTime.Now;
                DateTime ToDateToThisWeek = DateTime.Now;
                DateTime FromDateToLastWeek = DateTime.Now;
                DateTime ToDateToLastWeek = DateTime.Now;
                // xet thu cho ngay hien tai
                string ThuTrongtuan = c.DayOfWeek.ToString();
                if (ThuTrongtuan == "Monday")
                {
                    FromDateToThisWeek = c;
                    ToDateToThisWeek = c.AddDays(6);
                    FromDateToLastWeek = c.AddDays(-7);
                    ToDateToLastWeek = c.AddDays(-1);
                }
                if (ThuTrongtuan == "Tuesday")
                {
                    FromDateToThisWeek = c.AddDays(-1);
                    ToDateToThisWeek = c.AddDays(5);
                    FromDateToLastWeek = c.AddDays(-8);
                    ToDateToLastWeek = c.AddDays(-2);
                }
                if (ThuTrongtuan == "Wednesday")
                {
                    FromDateToThisWeek = c.AddDays(-2);
                    ToDateToThisWeek = c.AddDays(4);
                    FromDateToLastWeek = c.AddDays(-9);
                    ToDateToLastWeek = c.AddDays(-3);
                }
                if (ThuTrongtuan == "Thursday")
                {
                    FromDateToThisWeek = c.AddDays(-3);
                    ToDateToThisWeek = c.AddDays(3);
                    FromDateToLastWeek = c.AddDays(-10);
                    ToDateToLastWeek = c.AddDays(-4);
                }
                if (ThuTrongtuan == "Friday")
                {
                    FromDateToThisWeek = c.AddDays(-4);
                    ToDateToThisWeek = c.AddDays(2);
                    FromDateToLastWeek = c.AddDays(-11);
                    ToDateToLastWeek = c.AddDays(-5);
                }
                if (ThuTrongtuan == "Saturday")
                {
                    FromDateToThisWeek = c.AddDays(-5);
                    ToDateToThisWeek = c.AddDays(1);
                    FromDateToLastWeek = c.AddDays(-12);
                    ToDateToLastWeek = c.AddDays(-6);
                }
                if (ThuTrongtuan == "Sunday")
                {
                    FromDateToThisWeek = c.AddDays(-6);
                    ToDateToThisWeek = c;
                    FromDateToLastWeek = c.AddDays(-13);
                    ToDateToLastWeek = c.AddDays(-7);
                }
                Tuple<DateTime, DateTime, DateTime, DateTime> regain = new Tuple<DateTime, DateTime, DateTime, DateTime>(FromDateToThisWeek, ToDateToThisWeek, FromDateToLastWeek, ToDateToLastWeek);
                return regain;
            }
        }
        public Tuple<double, double, double, double, double> CashoutStatisics()
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                double LastCashout = 0;
                double Today = 0;
                double Yesterday = 0;
                double ThisWeek = 0;
                double LastWeek = 0;
                DateTime yesterday = DateTime.Now.AddDays(-1);
                // lay dong moi nhat
                var _get1RowTowalletHistory = db.Withdrawals.Where(w => w.Status == 1 && w.Amout > 0).OrderByDescending(s => s.Id).FirstOrDefault();
                if (_get1RowTowalletHistory != null)
                    LastCashout = Convert.ToDouble(_get1RowTowalletHistory.Amout);
                // lay tong tien trong ngay hom nay
                var _getTodayForWalletHistory = db.Withdrawals.Where(w => w.Create_Date != null && w.Create_Date.Value.Day == DateTime.Now.Day && w.Create_Date.Value.Month == DateTime.Now.Month && w.Create_Date.Value.Year == DateTime.Now.Year && w.Amout > 0 && w.Status==1).ToList();
                if (_getTodayForWalletHistory.Count > 0)
                    Today = Convert.ToDouble(_getTodayForWalletHistory.Sum(s => s.Amout));
                // lay tong tien trong ngay hom qua
                var _getYesterdayToWallethistory = db.Withdrawals.Where(w => w.Create_Date.Value.Day == yesterday.Day && w.Create_Date.Value.Month == yesterday.Month && w.Create_Date.Value.Year == yesterday.Year && w.Status==1 && w.Amout > 0).ToList();
                if (_getYesterdayToWallethistory.Count > 0)
                    Yesterday = Convert.ToDouble(_getYesterdayToWallethistory.Sum(s => s.Amout));
                // lay ra nhung thang co ngay tao khac null
                var _ListWithdrawals = db.Withdrawals.Where(w => w.Create_Date != null && w.Status==1).ToList();
                if (_ListWithdrawals.Count > 0)
                {
                    var _ListFromToDateofWeek = _GetThisWeekAndLastWeek(DateTime.Now);
                    if (_ListFromToDateofWeek != null)
                    {
                        DateTime FromDateThisWeek = _ListFromToDateofWeek.Item1.Date;
                        DateTime ToDateThisWeek = _ListFromToDateofWeek.Item2.Date;
                        DateTime FromDateLastWeek = _ListFromToDateofWeek.Item3.Date;
                        DateTime ToDateLastWeek = _ListFromToDateofWeek.Item4.Date;
                        // lay tong tien trong tuan truoc
                        var _getThisWeekToWallethistory = _ListWithdrawals.Where(w => w.Create_Date.Value.Date >= FromDateThisWeek && w.Create_Date.Value.Date <= ToDateThisWeek).ToList();
                        if (_getThisWeekToWallethistory.Count > 0)
                            ThisWeek = Convert.ToDouble(_getThisWeekToWallethistory.Sum(s => s.Amout));
                        // lay tong tien trong tuan nay
                        var _getLastWeekToWalletHistory = _ListWithdrawals.Where(w => w.Create_Date.Value.Date >= FromDateLastWeek && w.Create_Date.Value.Date <= ToDateLastWeek).ToList();
                        if (_getLastWeekToWalletHistory.Count > 0)
                            LastWeek = Convert.ToDouble(_getLastWeekToWalletHistory.Sum(s => s.Amout));
                    }
                }
                Tuple<double, double, double, double, double> regain = new Tuple<double, double, double, double, double>(LastCashout, Today, Yesterday, ThisWeek, LastWeek);
                return regain;
            }
        }
        public Tuple<double, double, double, double, double, double> FeeThisWeekStatisics()
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                double StarterThisWeek = 0;
                double InvestorThisWeek = 0;
                double BussinessThisWeek = 0;
                double StartThisWeek = 0;
                double StartLastWeek = 0;
                double StarterLastWeek = 0;
                double InvestorLastWeek = 0;
                double BussinessLastWeek1 = 0;
                // tinh nhung thang user co lv update trong tuan nay
                var _checkWeek = _GetThisWeekAndLastWeek(DateTime.Now);
                // lay ra danh sach user da duoc update Level
                var _ListUserUpdateLevel = db.UserProfiles.Where(w => w.LevelUpdate != null).ToList();
                if (_ListUserUpdateLevel.Count > 0)
                {
                    // kiem tra lay ra ds user update level trong tuan nay
                    var _ListUserUpdateLevelThisWeek = _ListUserUpdateLevel.Where(w => w.LevelUpdate.Value.Date >= _checkWeek.Item1.Date && w.LevelUpdate.Value.Date <= _checkWeek.Item2.Date).ToList();
                    // tu danh sach nay ta so voi bang user level de tinh ra cac loai tien theo level
                    if (_ListUserUpdateLevelThisWeek.Count > 0)
                    {
                        var _checkStarter = from u in _ListUserUpdateLevelThisWeek
                                            join ulv in db.UserLevels
                                            on u.Level equals ulv.Id
                                            select new
                                            {
                                                UserID = u.UserId,
                                                Level = u.Level,
                                                Amount = ulv.Amount,
                                                Name = ulv.Name,
                                            };
                        var _SumAmoutForLevelStart = _checkStarter.Where(w => w.Level == 1).ToList();
                        if (_SumAmoutForLevelStart.Count > 0)
                            StartThisWeek = Convert.ToDouble(_SumAmoutForLevelStart.Sum(s => s.Amount));
                        var _SumAmountForLevelStater = _checkStarter.Where(w => w.Level == 2).ToList();
                        if (_SumAmountForLevelStater.Count > 0)
                            StarterThisWeek = Convert.ToDouble(_SumAmountForLevelStater.Sum(s => s.Amount));
                        var _SumAmountForUSInvestor = _checkStarter.Where(w => w.Level == 3).ToList();
                        if (_SumAmountForUSInvestor.Count > 0)
                            InvestorThisWeek = Convert.ToDouble(_SumAmountForUSInvestor.Sum(s => s.Amount));
                        var _SumAmountFotLevelBussiness = _checkStarter.Where(w => w.Level == 4).ToList();
                        if (_SumAmountFotLevelBussiness.Count > 0)
                            BussinessThisWeek = Convert.ToDouble(_SumAmountFotLevelBussiness.Sum(s => s.Amount));
                    }
                    // tuan truoc
                    var _ListUserUpdateLevelLastWeek = _ListUserUpdateLevel.Where(w => w.LevelUpdate.Value.Date >= _checkWeek.Item3.Date && w.LevelUpdate.Value.Date <= _checkWeek.Item4.Date).ToList();
                    if (_ListUserUpdateLevelLastWeek.Count > 0)
                    {
                        var _checkStarter1 = from u in _ListUserUpdateLevelLastWeek
                                             join ulv in db.UserLevels
                                             on u.Level equals ulv.Id
                                             select new
                                             {
                                                 UserID = u.UserId,
                                                 Level = u.Level,
                                                 Amount = ulv.Amount,
                                                 Name = ulv.Name,
                                             };
                        var _SumAmoutForLevelStart = _checkStarter1.Where(w => w.Level == 1).ToList();
                        if (_SumAmoutForLevelStart.Count > 0)
                            StartLastWeek = Convert.ToDouble(_SumAmoutForLevelStart.Sum(s => s.Amount));
                        var _SumAmountForLevelStater = _checkStarter1.Where(w => w.Level == 2).ToList();
                        if (_SumAmountForLevelStater.Count > 0)
                            StarterLastWeek = Convert.ToDouble(_SumAmountForLevelStater.Sum(s => s.Amount));
                        var _SumAmountForUSInvestor = _checkStarter1.Where(w => w.Level == 3).ToList();
                        if (_SumAmountForUSInvestor.Count > 0)
                            InvestorLastWeek = Convert.ToDouble(_SumAmountForUSInvestor.Sum(s => s.Amount));
                        var _SumAmountFotLevelBussiness = _checkStarter1.Where(w => w.Level == 4).ToList();
                        if (_SumAmountFotLevelBussiness.Count > 0)
                            BussinessLastWeek1 = Convert.ToDouble(_SumAmountFotLevelBussiness.Sum(s => s.Amount));
                    }
                }
                Tuple<double, double, double, double, double, double> regain = new Tuple<double, double, double, double, double, double>(StarterThisWeek, InvestorThisWeek, BussinessThisWeek, StarterLastWeek, InvestorLastWeek, BussinessLastWeek1);
                return regain;
            }
        }
        public Tuple<double, double, double, double, double, double, double> AddFundForWeek(DateTime c)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                double AddfundMonDay = 0;
                double AddFundTuesday = 0;
                double AddFundWednesday = 0;
                double AddFundThursday = 0;
                double AddFundFriday = 0;
                double AddFundSaturday = 0;
                double AddFundSunday = 0;
                // check cac ngay trong tuan nay           
                var monday = c.Date;
                var tuesday = c.AddDays(1);
                var wednesday = c.AddDays(2);
                var thursday = c.AddDays(3);
                var friday = c.AddDays(4);
                var Satturday = c.AddDays(5);
                var Sunday = c.AddDays(6);
                // tinh tien ngay thu 2 dau tuan
                var _CheckCreateDate = db.WalletHistories.Where(w => w.CreateDate != null).ToList();
                if (_CheckCreateDate.Count > 0)
                {
                    var _AdFundAmountMonday = _CheckCreateDate.Where(w => w.CreateDate.Date == monday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AdFundAmountMonday.Count > 0)
                        AddfundMonDay = Convert.ToDouble(_AdFundAmountMonday.Sum(s => s.Amount));
                    // tinh tien ngay thu 3 
                    var _AddFundAmountTuesday = _CheckCreateDate.Where(w => w.CreateDate.Date == tuesday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AddFundAmountTuesday.Count > 0)
                        AddFundTuesday = Convert.ToDouble(_AddFundAmountTuesday.Sum(s => s.Amount));
                    // tinh tien ngay thu 4
                    var _AddFundAmountWednesday = _CheckCreateDate.Where(w => w.CreateDate.Date == wednesday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AddFundAmountWednesday.Count > 0)
                        AddFundWednesday = Convert.ToDouble(_AddFundAmountWednesday.Sum(s => s.Amount));
                    // tinh tien ngay thu 5
                    var _AddFundAmountThursday = _CheckCreateDate.Where(w => w.CreateDate.Date == thursday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AddFundAmountThursday.Count > 0)
                        AddFundThursday = Convert.ToDouble(_AddFundAmountThursday.Sum(s => s.Amount));
                    // tinh tien ngay thu 6
                    var _AddFundAmountFriday = _CheckCreateDate.Where(w => w.CreateDate.Date == friday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AddFundAmountFriday.Count > 0)
                        AddFundFriday = Convert.ToDouble(_AddFundAmountFriday.Sum(s => s.Amount));
                    // tinh tien ngay thu 7
                    var _AddFundAmountSatterday = _CheckCreateDate.Where(w => w.CreateDate.Date == Satturday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AddFundAmountSatterday.Count > 0)
                        AddFundSaturday = Convert.ToDouble(_AddFundAmountSatterday.Sum(s => s.Amount));
                    // tinh tien theo ngay chu nhat
                    var _AddFundAmountSunday = _CheckCreateDate.Where(w => w.CreateDate.Date == Sunday.Date && w.WalletId == 1 && w.Amount > 0).ToList();
                    if (_AddFundAmountSunday.Count > 0)
                        AddFundSunday = Convert.ToDouble(_AddFundAmountSunday.Sum(s => s.Amount));
                }
                Tuple<double, double, double, double, double, double, double> regain = new Tuple<double, double, double, double, double, double, double>(AddfundMonDay, AddFundTuesday, AddFundWednesday, AddFundThursday, AddFundFriday, AddFundSaturday, AddFundSunday);
                return regain;
            }
        }
        public Tuple<double, double, double, double, double, double, double> CashoutForWeek(DateTime c)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                double CashoutMonDay = 0;
                double CashoutTuesday = 0;
                double CashoutWednesday = 0;
                double CashoutThursday = 0;
                double CashoutFriday = 0;
                double CashoutSaturday = 0;
                double CashoutSunday = 0;
                // check cac ngay trong tuan nay           
                var monday = c.Date;
                var tuesday = c.AddDays(1);
                var wednesday = c.AddDays(2);
                var thursday = c.AddDays(3);
                var friday = c.AddDays(4);
                var Satturday = c.AddDays(5);
                var Sunday = c.AddDays(6);
                // tinh tien ngay thu 2 dau tuan
                var _CheckCreateDate = db.Withdrawals.Where(w => w.Create_Date != null).ToList();
                if (_CheckCreateDate.Count > 0)
                {
                    var _AdFundAmountMonday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == monday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AdFundAmountMonday.Count > 0)
                        CashoutMonDay = Convert.ToDouble(_AdFundAmountMonday.Sum(s => s.Amout));
                    // tinh tien ngay thu 3 
                    var _AddFundAmountTuesday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == tuesday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AddFundAmountTuesday.Count > 0)
                        CashoutTuesday = Convert.ToDouble(_AddFundAmountTuesday.Sum(s => s.Amout));
                    // tinh tien ngay thu 4
                    var _AddFundAmountWednesday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == wednesday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AddFundAmountWednesday.Count > 0)
                        CashoutWednesday = Convert.ToDouble(_AddFundAmountWednesday.Sum(s => s.Amout));
                    // tinh tien ngay thu 5
                    var _AddFundAmountThursday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == thursday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AddFundAmountThursday.Count > 0)
                        CashoutThursday = Convert.ToDouble(_AddFundAmountThursday.Sum(s => s.Amout));
                    // tinh tien ngay thu 6
                    var _AddFundAmountFriday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == friday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AddFundAmountFriday.Count > 0)
                        CashoutFriday = Convert.ToDouble(_AddFundAmountFriday.Sum(s => s.Amout));
                    // tinh tien ngay thu 7
                    var _AddFundAmountSatterday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == Satturday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AddFundAmountSatterday.Count > 0)
                        CashoutSaturday = Convert.ToDouble(_AddFundAmountSatterday.Sum(s => s.Amout));
                    // tinh tien theo ngay chu nhat
                    var _AddFundAmountSunday = _CheckCreateDate.Where(w => w.Create_Date.Value.Date == Sunday.Date && w.WalletId == 1 && w.Amout > 0).ToList();
                    if (_AddFundAmountSunday.Count > 0)
                        CashoutSunday = Convert.ToDouble(_AddFundAmountSunday.Sum(s => s.Amout));
                }
                Tuple<double, double, double, double, double, double, double> regain = new Tuple<double, double, double, double, double, double, double>(CashoutMonDay, CashoutTuesday, CashoutWednesday, CashoutThursday, CashoutFriday, CashoutSaturday, CashoutSunday);
                return regain;
            }
        }
        public PerfectMoneyUser ChooosePerfectMoney(int userid)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                return db.PerfectMoneyUsers.Where(w => w.UserId == userid).FirstOrDefault();
            }
        }
        public List<PerfectMoneyUser> GetListPerfectMoneyUser()
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                return db.PerfectMoneyUsers.ToList();
            }
        }
    } 
    public class ProfitUser
    {
        public int Stt { get; set; }

        public DateTime? Date { get; set; }

        public double? Amount { get; set; }

        public string Description { get; set; }

        public int UserId { get; set; }

        public string UserName { get; set; }

        public string HoTen { get; set; }
    }
}
